package uit.jee.lib.access.mapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import uit.jee.lib.dto.AccountDTO;

/**
 *
 * @author ngloc_it
 */
public class AccountMapper extends MapperDB{
    public AccountMapper() throws Exception {
        super();
    }

    private void InitAccountDTOFromRs(AccountDTO account, ResultSet rs) throws Exception{
        if((rs != null) && (account != null)){
            account.setAccountId(rs.getString(AccountDTO.ACCOUNT_ID));
            account.setAccountMoney(rs.getInt(AccountDTO.ACCOUNT_MONEY));
            account.setPassword(rs.getString(AccountDTO.ACCOUNT_PASSWORD));
            account.setReaderId(rs.getString(AccountDTO.ACCOUNT_READER_ID));
            account.setUserName(rs.getString(AccountDTO.ACCOUNT_USERNAME));
        }
    }

    /**
     * Check if a account already login in system
     * @param userName user name
     * @param password password
     * @return true or false
     * @throws Exception
     */
    public boolean Login(String userName, String password) throws Exception{
        boolean result = false;
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT TenDangNhap, MatKhau FROM booklibrary.taikhoan WHERE ");
            sql.append(" TenDangNhap = '").append(userName).append("'");
            sql.append(" and MatKhau = '").append(password).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            if ((rs != null) && (rs.next())) {
                result = true;
            }
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
        return result;
    }

    /**
     * Get all user in database
     * @return
     * @throws Exception
     */
    public ArrayList<AccountDTO> getAllAccount()throws Exception{
        ArrayList<AccountDTO> results = new ArrayList<AccountDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Select * from booklibrary.taikhoan");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            while((rs!=null) && rs.next()){
                AccountDTO accountTemp = new AccountDTO();
                InitAccountDTOFromRs(accountTemp, rs);
                results.add(accountTemp);
            }
        }catch(Exception ex){
            throw ex;
        }
        return results;
    }

    /**
     * Get an account by it's username
     * @param userName
     * @return
     * @throws Exception
     */
    public AccountDTO GetAccount(String userName) throws Exception{
        AccountDTO account = new AccountDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Select * from booklibrary.taikhoan Where");
            sql.append(" TenDangNhap = '").append(userName).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            if((rs!=null) && rs.next()){
                InitAccountDTOFromRs(account, rs);
            }
        }catch(Exception ex){
                throw ex;
        }
        return account;
    }

    /**
     * Insert an account into database
     * @param account
     * @throws Exception
     */
    public void Insert(AccountDTO account) throws Exception{
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("Insert into booklibrary.taikhoan values( ");
            sql.append("'").append(account.getAccountId()).append("', ");
            sql.append("'").append(account.getReaderId()).append("', ");
            sql.append("'").append(account.getUserName()).append("', ");
            sql.append("'").append(account.getPassword()).append("', ");
            sql.append(account.getAccountMoney()).append(")");      
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }
        catch (Exception e) {
            throw e;
        }
    }

    /**
     * Insert a list of account into database
     * @param accounts list of account for insert
     * @throws Exception
     */
    public void Insert(List<AccountDTO> accounts) throws Exception{
        if(accounts == null)
            throw new Exception("Accounts Null");
        if(accounts.isEmpty()){
            throw new Exception("Accounts Empty");
        }
        try {
            int numAccount = accounts.size();
            Connection con = getConnection();
            con.setAutoCommit(false);
            for(int i = 0; i < numAccount; i++){
                StringBuffer sql = new StringBuffer();
                final AccountDTO acc = accounts.get(i);
                sql.append("Insert into booklibrary.taikhoan values( ");
                sql.append("'").append(acc.getAccountId()).append("', ");
                sql.append("'").append(acc.getReaderId()).append("', ");
                sql.append("'").append(acc.getUserName()).append("', ");
                sql.append("'").append(acc.getPassword()).append("', ");
                sql.append(acc.getAccountMoney()).append(")");
                PreparedStatement stmt = con.prepareStatement(sql.toString());
                stmt.execute();
                stmt.close();
            }
            con.commit();
        }
        catch (Exception e) {
            throw e;
        }
    }

    /**
     *
     * @param account
     * @throws Exception
     */
    public void Update(AccountDTO account) throws Exception{        
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Update booklibrary.taikhoan Set ");
            sql.append(" MaDocGia = '").append(account.getReaderId()).append("', ");
            sql.append(" TenDangNhap = '").append(account.getUserName()).append("',");
            sql.append(" MatKhau = '").append(account.getPassword()).append("',");
            sql.append(" TaiKhoanTien = ").append(account.getAccountMoney());

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }

    }

    /**
     *
     * @param accounts
     * @throws Exception
     */
    public void Update(List<AccountDTO> accounts) throws Exception{
        //NOT DONE
    }

    /**
     *
     * @param account
     * @throws Exception
     */
    public void Delete(AccountDTO account) throws Exception{
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Delete * from booklibrary.taikhoan Where MaTaiKhoan = '");
            sql.append(account.getAccountId()).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
    }

    public void Delete(String id) throws Exception{
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Delete * from booklibrary.taikhoan Where MaTaiKhoan = '");
            sql.append(id).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
    }

    /**
     *
     * @param accounts
     * @throws Exception
     */
    public void Delete(List<AccountDTO> accounts) throws Exception{

    }
}